﻿using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Model.Data;
using Wedn.Utility;

namespace Wedn.DAL
{
    /// <summary>
    /// 关系表操作类(初步审核通过)
    /// 此类为抽象类, 不允许被实例化, 需要使用时直接调用
    /// </summary>
    public abstract class RelationsDAO
    {
        #region 增加关系
        /// <summary>
        /// 向关系表中插入一行数据, 返回受影响行数
        /// </summary>
        /// <param name="relation">要插入的对象</param>
        /// <returns>受影响行数</returns>
        public static int Insert(Relation relation)
        {
            string sql = @"INSERT INTO [Blog_Relations]
                                    (
                                        [object_id],
                                        [object_type],
                                        [term_id]
                                    )
                                   VALUES 
                                    (
                                        @ObjectId,
                                        @ObjectType,
                                        @TermId
                                    );";
            return ExecuteNonQuery(relation, sql);
        }
        #endregion

        #region 删除关系

        #region 删除一条
        /// <summary>
        /// 根据id删除对应的关系记录, 返回受影响行数
        /// </summary>
        /// <param name="relationid">要删除记录的ID</param>
        /// <returns>受影响行数</returns>
        public static int Delete(int relationid)
        {
            string sql = "DELETE FROM [Blog_Relations] WHERE [relation_id]=@Id;";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", relationid));
        }
        #endregion

        #region 删除多条
        /// <summary>
        /// 根据id拼接的字符串删除对应的关系记录, 返回受影响行数
        /// </summary>
        /// <param name="ids">要删除记录的ID字符串, 以逗号分隔"1,2,3....."</param>
        /// <returns>受影响行数</returns>
        public static int Delete(string ids)
        {
            string sql = "DELETE FROM [Blog_Relations] WHERE [relation_id] in (" + ids + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }
        #endregion

        #endregion

        #region 更新关系
        /// <summary>
        /// 更新一个关系对象, 返回受影响行数
        /// </summary>
        /// <param name="relation">要更新的对象</param>
        /// <returns>受影响行数</returns>
        public static int Update(Relation relation)
        {
            string sql = @"UPDATE 
                                        [Blog_Relations]
                                   SET
                                        [object_id]=@ObjectId,
                                        [object_type]=@ObjectType,
                                        [term_id]=@TermId
                                   WHERE
                                        [relation_id]=@Id;";
            return ExecuteNonQuery(relation, sql);
        }
        #endregion

        #region 选取关系

        #region 获取表中主键最大值
        /// <summary>
        /// 获取表中主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public static int GetMaxId()
        {
            return SqlHelper.SelectMaxId("relation_id", "Blog_Relations");
        }
        #endregion

        #region 选取全部关系
        /// <summary>
        /// 取出当前关系表中的所有关系信息集合
        /// </summary>
        /// <returns>包涵所有关系信息集合</returns>
        public static List<Relation> SelectRelations()
        {
            string sql = @"SELECT 
                                        [relation_id],
                                        [object_id],
                                        [object_type],
                                        [term_id]
                                    FROM
                                        [Blog_Relations];";
            return Select(sql);
        }
        #endregion

        #region 根据对象ID和对象类型选取全部关系集合
        /// <summary>
        /// 根据对象ID和对象类型取出当前关系表中的所有关系信息集合
        /// </summary>
        /// <returns>包涵指定对象关系信息集合</returns>
        public static List<Relation> SelectRelations(int objectid, string objectType)
        {
            string sql = @"SELECT 
                                        [relation_id],
                                        [object_id],
                                        [object_type],
                                        [term_id]
                                    FROM
                                        [Blog_Relations]
                                    WHERE
                                        [object_id]=@Id
                                    AND
                                        [object_type]=@Type;";
            return Select(sql,
                new SqlParameter("@Id", objectid),
                new SqlParameter("@Type", objectType)
                );
        }
        #endregion

        #region 根据ID选取关系对象
        /// <summary>
        /// 根据关系ID取出当前关系表中的关系信息对象
        /// </summary>
        /// <returns>关系信息对象</returns>
        public static Relation SelectRelation(int relationid)
        {
            string sql = @"SELECT 
                                        [relation_id],
                                        [object_id],
                                        [object_type],
                                        [term_id]
                                    FROM
                                        [Blog_Relations]
                                    WHERE
                                        [relation_id]=@Id;";
            return Select(sql, new SqlParameter("@Id", relationid))[0];
        }
        #endregion

        #region 根据标签ID取出当前关系表存在个数
        /// <summary>
        /// 根据标签ID取出当前关系表存在个数
        /// </summary>
        /// <returns>个数</returns>
        public static int SelectTermCount(int termid)
        {
            string sql = @"SELECT 
                                    COUNT
                                    (
                                        [term_id]
                                    )
                                    FROM
                                        [Blog_Relations]
                                    WHERE
                                        [term_id]=@Id;";
            return (int)SqlHelper.ExecuteScalar(sql, new SqlParameter("@Id", termid));
        }
        #endregion

        #endregion

        #region 私有操作方法

        #region select
        /// <summary>
        /// 私有选取数据方法
        /// </summary>
        /// <param name="sql">T-SQL语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>数据集合</returns>
        private static List<Relation> Select(string sql, params SqlParameter[] parameter)
        {
            List<Relation> list = new List<Relation>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                while (reader.Read())
                {
                    Relation relation = new Relation(
                        reader.GetInt32(0),
                        reader.GetInt32(1),
                        reader.GetString(2),
                        reader.GetInt32(3));
                    list.Add(relation);
                }
            }
            return list;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 私有方法执行写入数据库操作
        /// </summary>
        /// <param name="relation">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>受影响行数</returns>
        private static int ExecuteNonQuery(Relation relation, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@ObjectId", relation.ObjectId),
                new SqlParameter("@ObjectType", relation.ObjectType),
                new SqlParameter("@TermId", relation.TermId),
                new SqlParameter("@Id", relation.Id)
                );
        }
        #endregion

        #endregion
    }
}
